Large database tables are often partitioned to make the data stored in the table more manageable. The tables are typically partitioned based on a value of a given attribute in the data stored in the table, such as a chronological date or geographic region. To facilitate locating and storing data in the partitioned table, a partition metadata table typically is used to store information about the partitions in the database. Both global and local indexes can be used to speed up queries on the partitioned table. For queries with user-defined data types, and/or user-defined operations, user-defined indexes may also be used. For example, in some database systems, a spatial index is a kind of user-defined, or domain, index. SDO_GEOMETRY is a user-defined type and SDO_FILTER is a user-defined operator, which are built on top of a database.
Queries on a partitioned database can be handled according to many different query plans. For example, some query plans cause the query to be executed on each partition of the database. However, such a query plan results in significant overhead processing for execution of the query on the many partitions that do not contain any data relevant to the query. A reduction in processing overhead has been achieved by generating query plans in which certain queries on a partitioned database are executed by first accessing each of the individual partition's metadata to determine if the partition contains data that matches the query. Thus the query is executed on every partition's metadata. For the partitions that do not contain data that match the query predicate, no data is returned. While this approach can reduce the execution time and cost, overhead is still incurred for accessing each index partition in the database.
One type of data that is typically stored in partitioned database table is geographic or spatial data. A spatial index may be created to more efficiently process queries on spatial data. A geographic data table may include partitions that each represent, for example, a substantially contiguous 100 km by 100 km region. The partition metadata for a given partition typically stores a minimum bounding rectangle (MBR) for the data in the partition. The MBR is the smallest rectangle that completely encloses all of a defined spatial region. The root MBR for a partition is the smallest rectangle that completely encloses all of the spatial data in the partition. In the prior art query plan discussed above, a query plan is generated that accesses each spatial index partition and compares the MBR of the partition with the MBR of a query geometry object defined by a spatial query on the database. If the MBR intersects the MBR of the query geometry object, then the query is executed on the partition. If not, the query returns nothing. Thus the overhead for accessing each index partition is incurred in processing of the spatial query.